<?php

namespace Sdk\lib;

use DAO_MySql;

class Sql {

	const APIPrefix = '';

	var $prefix = '';
	var $new = array();
	var $old = array();

	const DataRe = array(
		['/^INSERT\s+(INTO)?\s*(.+?)\s+/i', 2],
		['/^UPDATE\s+(.+?)\s+/i', 1],
		['/^DELETE FROM\s+(.+?)\s+/i', 1],
	);

	public function __construct($prefix = '') {
		$this->prefix = $prefix;
	}

	public function getCreateData($sql) {
		preg_replace_callback("/CREATE\s+TABLE.+?(.+?)\s*\((.+?)\)\s*(ENGINE|TYPE)\s*=\s*(\w+)/is", function ($matches) {
			$table = $matches[1];
			$body = $matches[2];
			$footer = $matches[3] . '=' . $matches[4];
			if (substr($table, 0, 1) == '`') {
				$table = substr($table, 1, -1);
			}
			$sql = 'CREATE TABLE {TABLE_NAME} (' . $body . ') ' . $footer;
			$this->new[$table] = array(
				'sql' => $sql,
				'cols' => $this->_getCols($sql),
			);
		}, $sql);
	}

	private function _getCols($creatsql) {
		$creatsql = preg_replace("/ COMMENT '.*?'/i", '', $creatsql);
		preg_match("/\((.+)\)\s*(ENGINE|TYPE)\s*\=/is", $creatsql, $matchs);

		$cols = explode("\n", $matchs[1]);
		$newcols = array();
		foreach ($cols as $value) {
			$value = trim($value);
			if (empty($value)) continue;
			$value = $this->_remakesql($value);
			if (substr($value, -1) == ',') {
				$value = substr($value, 0, -1);
			}

			$vs = explode(' ', $value);
			$cname = $vs[0];

			if ($cname == 'KEY' || $cname == 'INDEX' || $cname == 'UNIQUE') {
				$name_length = strlen($cname);
				if ($cname == 'UNIQUE') $name_length = $name_length + 4;

				$subvalue = trim(substr($value, $name_length));
				$subvs = explode(' ', $subvalue);
				$subcname = $subvs[0];
				$newcols[$cname][$subcname] = trim(substr($value, ($name_length + 2 + strlen($subcname))));
			} elseif ($cname == 'PRIMARY') {
				$newcols[$cname] = trim(substr($value, 11));
			} else {
				$newcols[$cname] = trim(substr($value, strlen($cname)));
			}
		}
		return $newcols;
	}

	private function _remakesql($value) {
		$value = trim(preg_replace("/\s+/", ' ', $value));
		$value = str_replace(array('`', ', ', ' ,', '( ', ' )', 'mediumtext'), array('', ',', ',', '(', ')', 'text'), $value);
		return $value;
	}

	public function checkTable($tableName) {
		DAO_MySql::setSilent(true);
		try {
			$info = DAO_MySql::findBySql($tableName, 'SHOW CREATE TABLE ' . $tableName);
		} catch (Exception $e) {
			Logger::error($e);
		}
		DAO_MySql::setSilent(false);
		if (!$info) {
			return null;
		}
		$this->old[$info[0]['Table']] = array(
			'sql' => $info[0]['Create Table'],
			'cols' => $this->_getCols($info[0]['Create Table']),
		);
		return $info;
	}

	public function createTable($tableName, $sql) {
		try {
			$sql = str_replace('{TABLE_NAME}', '`' . $tableName . '`', $sql);
			DAO_MySql::execBySql($tableName, $sql);
		} catch (Exception $e) {
			Logger::error($e);
			return false;
		}
		return true;
	}

	public function compareCols($tableName) {
		$updates = array();

		$data = $this->new[$tableName];
		$allFields = array_keys($data['cols']);
		$old = &$this->old[$tableName]['cols'];
		foreach ($data['cols'] as $key => $value) {
			if ($key == 'PRIMARY') {
				if ($value != $old[$key]) {
					$updates[] = "ADD PRIMARY KEY $value";
				}
			} elseif ($key == 'KEY') {
				foreach ($value as $subkey => $subvalue) {
					if (!empty($old['KEY'][$subkey])) {
						if ($subvalue != $old['KEY'][$subkey]) {
							$updates[] = "DROP INDEX `$subkey`";
							$updates[] = "ADD INDEX `$subkey` $subvalue";
						}
					} else {
						$updates[] = "ADD INDEX `$subkey` $subvalue";
					}
				}
			} elseif ($key == 'UNIQUE') {
				foreach ($value as $subkey => $subvalue) {
					if (!empty($old['UNIQUE'][$subkey])) {
						if ($subvalue != $old['UNIQUE'][$subkey]) {
							$updates[] = "DROP INDEX `$subkey`";
							$updates[] = "ADD UNIQUE INDEX `$subkey` $subvalue";
						}
					} else {
						$updates[] = "ADD UNIQUE INDEX `$subkey` $subvalue";
					}
				}
			} else {
				if (!empty($old[$key])) {
					if (strtolower($value) != strtolower($old[$key])) {
						$updates[] = "CHANGE `$key` `$key` $value";
					}
				} else {
					$i = array_search($key, $allFields);
					$fieldPosition = $i > 0 ? 'AFTER `' . $allFields[$i - 1] . '`' : 'FIRST';
					$updates[] = "ADD `$key` $value $fieldPosition";
				}
			}
		}
		return $updates;
	}

	public function alterTable($tableName, $sql) {
		$sql = 'ALTER TABLE `table` ' . $sql;
		return $this->execBySql($tableName, $sql);
	}

	public function execBySql($tableName, $sql) {
		try {
			DAO_MySql::execBySql($tableName, $sql);
		} catch (Exception $e) {
			Logger::error($e);
			return false;
		}
		return true;
	}

	public function getCurrentSql($sqls, $getVer) {
		$vers = array();
		$curVer = 0;
		foreach (explode("\n", $sqls) as $row) {
			$row = trim($row);
			preg_match_all('/^--\s*\[v(\d+)\]/', $row, $matches);
			if ($matches && $matches[1]) {
				$curVer = $matches[1][0];
				$vers[$curVer] = '';
			} elseif ($curVer > 0) {
				$vers[$curVer] .= $row . "\n";
			}
		}
		$return = '';
		foreach ($vers as $ver => $sql) {
			if ($getVer < $ver) {
				$return .= $sql . "\n";
			}
		}
		return [$return, $curVer];
	}

	public function runSqls($sqls) {
		$num = 0;
		$ret = array();
		$sqls = str_replace(";\r\n", ";\n", $sqls);
		foreach (explode(";\n", trim($sqls)) as $query) {
			$sql = '';
			$queries = explode("\n", trim($query));
			foreach ($queries as $query) {
				$sql .= (isset($query[0]) && $query[0] == '#') || (isset($query[1]) && isset($query[1]) && $query[0] . $query[1] == '--') ? '' : $query;
			}

			foreach (self::DataRe as $re) {
				if (!preg_match($re[0], $sql, $matches)) {
					continue;
				}
				$table = $matches[$re[1]];
				if (str_starts_with($table, '`')) {
					$table = substr($table, 1, -1);
				}
				$ret[$num] = [$table, $sql];
			}
			$num++;
		}
		foreach ($ret as $v) {
			$this->execBySql($v[0], $v[1]);
		}
	}

}